【PostgreSQL】で"numeric"型の入力構文が不正ですと表示される場合の対処法

概要

下記のようなSQLを実行したところ、次のエラーが出力された。

code:修正前のSQL.sql

COALESCE(

src.value_numeric,

CAST(src.value_text AS numeric(38, 10))

) AS converted_value

"numeric"型の入力構文が不正です: ""

原因

src.value_text が文字列型であり、

値として空文字（''）が格納されていた場合、

CAST('' AS numeric) が実行されてしまい、PostgreSQLでは変換できず上記のエラーが出力された。

他のDB（MySQLやOracleなど）では自動的にNULLとして扱われることもありますが、

PostgreSQLでは空文字を明示的にNULLへ変換する必要がある。

対処法

NULLIFを使って、空文字をNULLに変換してからキャストする。

code:修正後のSQL.sql

COALESCE(

src.value_numeric,

CAST(NULLIF(src.value_text, '') AS numeric(38, 10))

) AS converted_value

NULLIF(src.value_text, '')

値が空文字ならNULLを返し、それ以外は元の値を返す。

結果的にCAST(NULL AS numeric)となり、エラーを回避できる。

備考

このエラーは、文字列型の列をnumericやdate、timestampなどにキャストする際にも発生する。

ETL処理や移行スクリプトなど、型変換を伴うSQLではNULLIFを常に意識しておくと安全。